package com.wowangz.cms.systemmanage.databaseresource.api;

import java.io.IOException;
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.wowangz.cms.common.pojo.AjaxDataEntity;
import com.wowangz.cms.common.utils.SysConstant;
import com.wowangz.cms.component.datagrid.pojo.DataGrid;
import com.wowangz.cms.component.datagrid.pojo.DataResult;
import com.wowangz.cms.component.datagrid.pojo.DataStyle;
import com.wowangz.cms.component.datagrid.pojo.PangingParam;
import com.wowangz.cms.component.datagrid.utils.DataTypeConstant;
import com.wowangz.cms.component.datagrid.utils.PangingTools;
import com.wowangz.cms.syscore.IssTransactionContainer;
import com.wowangz.cms.syscore.api.BaseController;
import com.wowangz.cms.syscore.api.JSONFactory;
import com.wowangz.cms.syscore.utils.MessageResourcesUtils;
import com.wowangz.cms.syscore.utils.ServletUtils;
import com.wowangz.cms.systemmanage.databaseresource.pojo.DatabaseResource;
import com.wowangz.cms.systemmanage.databaseresource.service.DatabaseResourceService;

@Scope("prototype")
@Controller
public class DatabaseResourceController extends BaseController {
	
	@SuppressWarnings("unused")
	private static Logger logger = LoggerFactory.getLogger(DatabaseResourceController.class);
	
	@RequestMapping(value="/systemmanage/databaseresource/queryDatabaseResourceList.web")
	public String queryDatabaseResourceList(HttpServletRequest request, HttpServletResponse response, Model model) {
		return "/WEB-INF/jsp/systemmanage/databaseresource/databaseresourceList";
	}
	
	
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@RequestMapping(value="/systemmanage/databaseresource/queryDatabaseResourceListInfoDataGrid.json", method=RequestMethod.POST)
	public void queryDatabaseResourceListInfoDataGrid(HttpServletRequest request, HttpServletResponse response, Model model) {
		
		PangingParam pangingParam = null;
		DataResult  dataResult = new DataResult();
		DataGrid dataGrid = null;

		try {
			pangingParam = (PangingParam) ServletUtils.getParametersToBaseEntity(request, PangingParam.class);
			HashMap<String, Object> map = (HashMap) ServletUtils.getParametersToHashMap(request);
			dataGrid = new DataGrid();
			dataGrid.setPangingParam(pangingParam);
			
			map.put("startrow",dataGrid.getStartrow());
			map.put("endrow", dataGrid.getEndrow());
			map.put("rows", dataGrid.getRows());
			map.put("sort", dataGrid.getSort());
			map.put("order", dataGrid.getOrder());
			
			DatabaseResourceService databaseresourceService = (DatabaseResourceService)IssTransactionContainer.getInstance(DatabaseResourceService.class,false);
			long resultCount = databaseresourceService.findResultCount(map);
			List<DatabaseResource> resultList = databaseresourceService.findResultList(map);
			
			DataStyle dataStyle = new DataStyle();
			dataStyle.setPageIndex(pangingParam.getPage());
			dataStyle.setDataCount(resultCount);
			dataStyle.setDataList(resultList);

			dataStyle.addDepict("id", DataTypeConstant.LONG);
			dataStyle.addDepict("ip", DataTypeConstant.STRING);
			dataStyle.addDepict("port", DataTypeConstant.LONG);
			dataStyle.addDepict("dbname", DataTypeConstant.STRING);
			dataStyle.addDepict("username", DataTypeConstant.STRING);
			dataStyle.addDepict("password", DataTypeConstant.STRING);
			dataStyle.addDepict("totalconn", DataTypeConstant.LONG);
			dataStyle.addDepict("usedconn", DataTypeConstant.LONG);
			dataResult = PangingTools.buildResultPagerInfo(dataStyle);
			
			if (pangingParam.getReportExportType() == 0) {
				response.getWriter().write(JSONFactory.toJSONString(dataResult, userContext));
			}
			
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	
	@RequestMapping(value="/systemmanage/databaseresource/toAddDatabaseResource.web")
	public String toAddDatabaseResource(HttpServletRequest request, HttpServletResponse response, Model model) {
		return "/WEB-INF/jsp/systemmanage/databaseresource/databaseresourceAdd";
	}
	
	/**
	 * 验证IP是否重复
	 * @param request
	 * @param response
	 * @param model
	 */
	@RequestMapping(value = "/systemmanage/databaseresource/valiDatabaseResource.json", method = { RequestMethod.GET, RequestMethod.POST })
	public void validattionProvince(HttpServletRequest request, HttpServletResponse response, Model model) {
		AjaxDataEntity ajaxDataEntity = new AjaxDataEntity();
		try {
			StringBuffer errorMsg = new StringBuffer();
			HashMap<String, Object> map = new HashMap<String, Object>();
			DatabaseResource databaseResource = (DatabaseResource) ServletUtils.getParametersToBaseEntity(request, DatabaseResource.class);
			
			DatabaseResourceService databaseResourceService = (DatabaseResourceService) IssTransactionContainer.getInstance(DatabaseResourceService.class, false);
			map.put("statusid", SysConstant.RecordStatus.VALID);
			map.put("ip", databaseResource.getIp());
			if(null != databaseResource.getId()){
				map.put("id", databaseResource.getId());
			}
			map.put("ip", databaseResource.getIp());
			if(databaseResourceService.valiDatabaseResource(map)){
				errorMsg.append(MessageResourcesUtils.getMessage(request, "systemmanage.databasebinding.alert"));
			}
			map.remove("ip");
			
			if(StringUtils.isNotEmpty(errorMsg.toString())){
				userContext.setErrorMessage(errorMsg.toString());
			}
			response.getWriter().write(JSONFactory.toJSONString(ajaxDataEntity, userContext));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	@RequestMapping(value="/systemmanage/databaseresource/addDatabaseResource.web", method={RequestMethod.GET,RequestMethod.POST})
	public String addDatabaseResource(HttpServletRequest request, HttpServletResponse response, Model model) {
		DatabaseResource databaseresource = null;
		try {
			databaseresource = (DatabaseResource) ServletUtils.getParametersToBaseEntity(request, DatabaseResource.class);
			DatabaseResourceService databaseresourceService = (DatabaseResourceService)IssTransactionContainer.getInstance(DatabaseResourceService.class,false);
			databaseresource.setInputtime(new Date());
			databaseresource.setInputuserid(userContext.getLoginUser().getId());
			databaseresource.setModifytime(new Date());
			databaseresource.setModifyuserid(userContext.getLoginUser().getId());
			databaseresource.setStatusid(SysConstant.RecordStatus.VALID);
			databaseresourceService.addResource(databaseresource);
			userContext.setInfoMessage(MessageResourcesUtils.getMessage(request,"alertinfo.common.java.alert1"));
			userContext.setCloseWindow(true);
		} catch(Exception e) {
			e.printStackTrace();
			userContext.setErrorMessage(MessageResourcesUtils.getMessage(request,"alertinfo.common.java.alert2"));
		}
		return "/WEB-INF/jsp/systemmanage/databaseresource/databaseresourceAdd";
	}
	
	
	@RequestMapping(value="/systemmanage/databaseresource/deleteDatabaseResource.json", method={RequestMethod.GET,RequestMethod.POST})
	public void deleteDatabaseResource(HttpServletRequest request, HttpServletResponse response, Model model) {
		AjaxDataEntity ajaxDataEntity = new AjaxDataEntity();
		try {
			String databaseresourceId = getRequest().getParameter("id");
			DatabaseResourceService databaseresourceService = (DatabaseResourceService)IssTransactionContainer.getInstance(DatabaseResourceService.class,false);
			databaseresourceService.delete(databaseresourceId);
			userContext.setInfoMessage(MessageResourcesUtils.getMessage(request,"alertinfo.common.java.alert3"));
			response.getWriter().write(JSONFactory.toJSONString(ajaxDataEntity, userContext));
		} catch(Exception e) {
			e.printStackTrace();
			userContext.setInfoMessage(MessageResourcesUtils.getMessage(request,"alertinfo.common.java.alert4"));
		}
	}
	
	
	@RequestMapping(value="/systemmanage/databaseresource/toUpdateDatabaseResource.web")
	public String toUpdateDatabaseResource(HttpServletRequest request, HttpServletResponse response, Model model) {
		try {
			String databaseresourceId = getRequest().getParameter("id");
			DatabaseResourceService databaseresourceService = (DatabaseResourceService)IssTransactionContainer.getInstance(DatabaseResourceService.class,false);
			DatabaseResource databaseresource = databaseresourceService.findDatabaseResourceById(databaseresourceId);
			model.addAttribute("db", databaseresource);
		} catch(Exception e) {
			e.printStackTrace();
		}
		return "/WEB-INF/jsp/systemmanage/databaseresource/databaseresourceUpdate";
	}
	
	@RequestMapping(value="/systemmanage/databaseresource/updateDatabaseResource.web", method={RequestMethod.GET,RequestMethod.POST})
	public String updateDatabaseResource(HttpServletRequest request, HttpServletResponse response, Model model) {
		DatabaseResource databaseresource = null;
		try {
			databaseresource = (DatabaseResource) ServletUtils.getParametersToBaseEntity(request, DatabaseResource.class);
			DatabaseResourceService databaseresourceService = (DatabaseResourceService)IssTransactionContainer.getInstance(DatabaseResourceService.class,false);
			databaseresource.setModifytime(new Date());
			databaseresource.setModifyuserid(userContext.getLoginUser().getId());
			databaseresourceService.update(databaseresource);
			userContext.setInfoMessage(MessageResourcesUtils.getMessage(request,"alertinfo.common.java.alert1"));
			userContext.setCloseWindow(true);
		} catch(Exception e) {
			e.printStackTrace();
			userContext.setErrorMessage(MessageResourcesUtils.getMessage(request,"alertinfo.common.java.alert2"));
		}
		return "/WEB-INF/jsp/systemmanage/databaseresource/databaseresourceUpdate";
	}
	
	@RequestMapping(value="/systemmanage/databaseresource/toViewDatabaseResource.web")
	public String toViewDatabaseResource(HttpServletRequest request, HttpServletResponse response, Model model) {
		try {
			String databaseresourceId = getRequest().getParameter("id");
			DatabaseResourceService databaseresourceService = (DatabaseResourceService)IssTransactionContainer.getInstance(DatabaseResourceService.class,false);
			DatabaseResource databaseresource = databaseresourceService.findDatabaseResourceById(databaseresourceId);
			model.addAttribute("db", databaseresource);
			//取得公司列表 前端选择公司分配资源
		} catch(Exception e) {
			e.printStackTrace();
		}
		return "/WEB-INF/jsp/systemmanage/databaseresource/databaseorg";
	}
	
	/**
	 * 详细页面中测试连接是否成功
	 * @param request
	 * @param response
	 * @param model
	 */
	@RequestMapping(value = "/systemmanage/databaseresource/testCommon.json", method = { RequestMethod.GET, RequestMethod.POST })
	public void testCommon(HttpServletRequest request, HttpServletResponse response, Model model) {
		AjaxDataEntity ajaxDataEntity = new AjaxDataEntity();
		DatabaseResource databaseResource = (DatabaseResource)ServletUtils.getParametersToBaseEntity(request, DatabaseResource.class);
		String ip = databaseResource.getIp();
		String port =String.valueOf(databaseResource.getPort());
		String driver="com.mysql.jdbc.Driver";   //获取mysql数据库的驱动类  
        String url="jdbc:mysql://"+ip+":"+port+"/test"; //连接数据库
        String name=databaseResource.getUsername();//连接mysql的用户名  
        String pwd=databaseResource.getPassword();//连接mysql的密码  
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
		try {
			Class.forName(driver);
			DriverManager.setLoginTimeout(5);//设置连接超时时间为5秒，超过5秒则超时
			conn = DriverManager.getConnection(url, name, pwd);// 获取连接对象
			if (!conn.isClosed()) {
				st = conn.createStatement();
				String sql = "select current_date as Systemtime;";
				rs = st.executeQuery(sql);
				while (rs.next()) {
					if (rs.getDate("Systemtime") != null) {
						System.out.println(rs.getDate("Systemtime"));
						userContext
								.setInfoMessage(MessageResourcesUtils
										.getMessage(request,
												"alertinfo.databaseresource.java.alert1"));
					}
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			userContext.setErrorMessage(MessageResourcesUtils.getMessage(
					request, "alertinfo.databaseresource.java.alert2"));
		} catch (SQLException e) {
			e.printStackTrace();
			userContext.setErrorMessage(MessageResourcesUtils.getMessage(
					request, "alertinfo.databaseresource.java.alert2"));
		} catch (Exception e) {
			e.printStackTrace();
			userContext.setErrorMessage(MessageResourcesUtils.getMessage(
					request, "alertinfo.databaseresource.java.alert2"));
		}finally{
			try {
				close(conn, st, rs);
				response.getWriter().write(JSONFactory.toJSONString(ajaxDataEntity, userContext));
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 列表页面测试连接是否成功
	 * @param request
	 * @param response
	 * @param model
	 */
	@RequestMapping(value = "/systemmanage/databaseresource/testCommonlist.json", method = { RequestMethod.GET, RequestMethod.POST })
	public void testCommonlist(HttpServletRequest request, HttpServletResponse response, Model model) {
		AjaxDataEntity ajaxDataEntity = new AjaxDataEntity();
		DatabaseResourceService databaseresourceService = (DatabaseResourceService)IssTransactionContainer.getInstance(DatabaseResourceService.class,false);
		String dbid = request.getParameter("dbid");

		DatabaseResource databaseResource = databaseresourceService
				.findDatabaseResourceById(dbid);
		String ip = databaseResource.getIp();
		String port = String.valueOf(databaseResource.getPort());
		String dbname = databaseResource.getDbname();
		String driver = "com.mysql.jdbc.Driver"; // 获取mysql数据库的驱动类
		String url = "jdbc:mysql://" + ip + ":" + port + "/" + dbname; // 连接数据库
		String name = databaseResource.getUsername();// 连接mysql的用户名
		String pwd = databaseResource.getPassword();// 连接mysql的密码
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			DriverManager.setLoginTimeout(5);// 设置连接超时时间为5秒，超过5秒则超时
			conn = DriverManager.getConnection(url, name, pwd);// 获取连接对象
			if (!conn.isClosed()) {
				st = conn.createStatement();
				String sql = "select current_date as Systemtime;";
				rs = st.executeQuery(sql);
				while (rs.next()) {
					if (rs.getDate("Systemtime") != null) {
						System.out.println(rs.getDate("Systemtime"));
						userContext
								.setInfoMessage(MessageResourcesUtils
										.getMessage(request,
												"alertinfo.databaseresource.java.alert1"));
					}
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			userContext.setErrorMessage(MessageResourcesUtils.getMessage(
					request, "alertinfo.databaseresource.java.alert2"));
		} catch (SQLException e) {
			e.printStackTrace();
			userContext.setErrorMessage(MessageResourcesUtils.getMessage(
					request, "alertinfo.databaseresource.java.alert2"));
		} catch (Exception e) {
			e.printStackTrace();
			userContext.setErrorMessage(MessageResourcesUtils.getMessage(
					request, "alertinfo.databaseresource.java.alert2"));
		} finally {
			try {
				close(conn, st, rs);
				response.getWriter().write(JSONFactory.toJSONString(ajaxDataEntity, userContext));
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 关闭jdbc连接对象
	 * @param conn
	 * @param ps
	 * @param st
	 * @param rs
	 */
	public void close(Connection conn,Statement st,ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(st!=null){
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

